import java.sql.*;
import java.util.ArrayList;

import sharedClasses.Action;
import sharedClasses.Match;
import sharedClasses.Play;

//This class is used to connect to the database.
//It is the only class that should do so.
public class DataIO 
{
	private static Connection con; //the active connection to the database
	private static int currentMatch; //the match that is currently being built
	private static int currentPlay; //the play that is currently being built
		
	//The constructor for DataIO. This will initialize and open the connection.
	//There is no need to do anything else to open the database connection.
	public DataIO()
	{
		try
        {
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@edgar2.cse.lehigh.edu:1521:cse241", "pjb313", "P818093150");
            con.setAutoCommit(false); //Otherwise with some of the more complicated inserts, if something later fails
                                      //we will be left with an inconsistent database
            System.out.println("CONNECTION SUCCESSFUL!");
        }
        catch (ClassNotFoundException e)
        {
            System.err.println(e);
        }
        catch (SQLException e)
        {
            System.err.println(e);
        }
        catch (Exception e)
        {
            System.err.println(e);
        }
        finally
        {
        }
	}
	
	//Returns the current match
	public int getCurrentMatch()
	{
		return currentMatch;
	}
	
	//Closes the database connection.
	//It is important to close the connection when a program is completely finished with it.
	public void close()
	{
		try
		{
			con.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	//Add a new match to the database
	//This match becomes the new current match
	public Match addMatch(String date, String opponent, String location)
	{
		Match match = null;
        PreparedStatement ps = null;
        String insertString = "insert into match (match_date, opponent, match_location) values(?, ?, ?)";
        try
        {
        	//Insert the new match into the database
            ps = con.prepareCall(insertString);
            ps.setString(1, date);
            ps.setString(2, opponent);
            ps.setString(3, location);
            ps.executeUpdate();
            
            //Get the match ID from the database
			Statement getId = con.createStatement();

			ResultSet results = getId.executeQuery("select max(to_number(id)) as id from match");
			while (results.next())
			{
				currentMatch = Integer.valueOf(results.getObject("id").toString());
			}
			
			//Create a match to return
			match = new Match(currentMatch);
			match.Date = date;
			match.Location = location;
			match.Opponent = opponent;;
            con.commit(); //commit the database changes
        }
        catch (SQLException e)
        {
            System.err.println(e);
            try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
        }
        finally
        {
            return match; //return the created match, will be something above threw an exception
        }
	}
	
	//Adds an empty match to the database.
	//Can be useful for testing, but you probably don't want to be using for real applications
	public Match addMatch()
	{
		return addMatch(null, null, null);
	}
	
	//Takes a match without an ID, adds it to the database, and then returns the new object
	public Match addMatch(Match match)
	{
		//Add the match to the database
		Match ret = addMatch(match.Date, match.Opponent, match.Location);
		//populate its children
		ret.children = new ArrayList<Play>();
		for (int i = 0; i< match.children.size(); i++)
		{
			addPlay(match.children.get(i));
			ret.children.add(match.children.get(i));
		}
		
		//return the new match object with the correct ID from the database
		return ret;
	}
	
	//Puts assigns the selected strings to a match
	//Could be useful for undo functionality, but we never implemented that, so for now, this is unused (and thus unpolished).
	//Note that this is only useful for updating match info, not the plays it contains
	public Match editMatch(int matchId, String date, String opponent, String location)
	{
		try
		{
			//Update the working match
			currentMatch = matchId;
			
			//Replace the working match in the database with the new match info
			PreparedStatement updateStatement = con.prepareStatement("update match set date = ?, opponent = ?, location = ? where matchId = ?");
			updateStatement.setString(1, date);
			updateStatement.setString(2, opponent);
			updateStatement.setString(3, location);
			updateStatement.setString(4, Integer.toString(matchId));
			
			updateStatement.executeUpdate();
			con.commit(); //because autocommit is set to false for atomicity
			
			//return that info
			Match match = new Match(currentMatch);
			match.Date = date;
			match.Location = location;
			match.Opponent = opponent;
			
			return match;
			
		}
		catch (SQLException e)
		{
			return null;
		}
	}
	
	//This will add a play to the database
	public void addPlay(Play play)
	{
		try
		{
			PreparedStatement insertStatement = con.prepareStatement("insert into play (result, rotation) values (?, ?)");
			insertStatement.setString(1, Integer.toString(play.getResult()));
			insertStatement.setInt(2, play.Rotation);
		
			insertStatement.executeUpdate();
			insertStatement.close();
		
			//Get the play ID
			Statement getId = con.createStatement();
			ResultSet results = null;
		
			//Gets the id of the newly inserted play
        	results = getId.executeQuery("select max(to_number(id)) as id from play");
        	while (results.next())
        	{
        		currentPlay = Integer.valueOf(results.getObject("id").toString());
        	}
        	results.close(); //make sure to close everything, otherwise, we can easily overflow our concurrent cursor allowance
        	getId.close();

			//Input the play to match mapping
			PreparedStatement matchPlayInsert = con.prepareStatement("insert into played_in (match_ID, play_ID) values (?, ?)");
			matchPlayInsert.setString(1, Integer.toString(currentMatch));
			matchPlayInsert.setString(2, Integer.toString(currentPlay));
			matchPlayInsert.executeUpdate();
			matchPlayInsert.close();
			//System.out.println("insert into played_in (match_ID, play_ID) values (?, ?)");
				
			int currentHalfplay = 0;
			for (int a = 0; a < play.Children.size(); a++)
			{
				//if a is 0, or a is greater than zero, and the last action did not end in a kill, but crossed the net in one way or another
				//then start a new halfplay
				if ((a == 0) || 
						((a > 0) && (play.Children.get(a - 1).getResult() == 0 
						&& ((play.Children.get(a - 1).getOrigin()[1] >= Court.MID_LINE && play.Children.get(a - 1).getDestination()[1] < Court.MID_LINE) 
								|| (play.Children.get(a - 1).getOrigin()[1] < Court.MID_LINE && play.Children.get(a - 1).getDestination()[1] >= Court.MID_LINE))))
						)
				{
					// New halfplay
					
					PreparedStatement halfplayInsert = con.prepareStatement("insert into halfplay (ID) values (?)");
					halfplayInsert.setString(1, "0");
					halfplayInsert.executeUpdate();
					halfplayInsert.close();
					
					Statement getHalfplayID = con.createStatement();
					results = getHalfplayID.executeQuery("select max(to_number(id)) as id from halfplay");
					while (results.next())
					{
						currentHalfplay = Integer.valueOf(results.getObject("id").toString());
					}
					results.close();
					getHalfplayID.close();
					
					//Input the play to halfplay mapping
					PreparedStatement hpPlayinsert = con.prepareStatement("insert into part_of (halfplay_id, play_ID) values (?, ?)");
					hpPlayinsert.setString(1, Integer.toString(currentHalfplay));
					hpPlayinsert.setString(2, Integer.toString(currentPlay));
					hpPlayinsert.executeUpdate();
					hpPlayinsert.close();
				}
				
				//Insert an action
				PreparedStatement actionInsert = con.prepareStatement("insert into action (origin_x, origin_y, destination_x, destination_y, result) values (?, ?, ?, ?, ?)");
				actionInsert.setString(1, Integer.toString(play.Children.get(a).getOrigin()[0]));
				actionInsert.setString(2, Integer.toString(play.Children.get(a).getOrigin()[1]));
				actionInsert.setString(3, Integer.toString(play.Children.get(a).getDestination()[0]));
				actionInsert.setString(4, Integer.toString(play.Children.get(a).getDestination()[1]));
				actionInsert.setString(5, Integer.toString(play.Children.get(a).getResult()));
				
				actionInsert.executeUpdate();
				actionInsert.close();
				
				Statement getActionID = con.createStatement();
				results = getActionID.executeQuery("select max(to_number(id)) as id from action");
				int actionId = 0;
				while (results.next())
				{
					actionId = Integer.valueOf(results.getObject("id").toString());
				}
				results.close();
				getActionID.close();
				
				//Input the action to halfplay mapping
				PreparedStatement hpActionInsert = con.prepareStatement("insert into base_of (halfplay_id, action_id) values (?, ?)");
				hpActionInsert.setString(1, Integer.toString(currentHalfplay));
				hpActionInsert.setString(2, Integer.toString(actionId));
				hpActionInsert.executeUpdate();
				hpActionInsert.close();
			}
			
			
			con.commit(); //because autocommit is set to false for atomicity
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		
	}
	
	//BE VERY CAREFUL USING THIS.
	//IT WILL EMPTY EVERY USEFUL TABLE IN THE DATABASE.
	//We use it when things get messy from testing
	//But it will nuke all data without discrimination
	public void clean()
	{
		try
		{
			Statement s = con.createStatement();
			s.executeUpdate("delete from action");
			//System.out.println("delete from action");
			s.executeUpdate("delete from halfplay");
			//System.out.println("delete from halfplay");
			s.executeUpdate("delete from play");
			//System.out.println("delete from play");
			s.executeUpdate("delete from match");
			//System.out.println("delete from match");
			s.executeUpdate("delete from part_of");
			//System.out.println("delete from part_of");
			s.executeUpdate("delete from base_of");
			//System.out.println("delete from base_of");
			s.executeUpdate("delete from played_in");
			//System.out.println("delete from played_in");
			con.commit();
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		
	}
	
	//Returns all the actions in the database
	public Action[] getAllActions()
	{
		ArrayList actions = new ArrayList();
		Action[] array = null;
		try
		{
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from action order by id");
			while (rs.next())
			{
				int originX = Integer.valueOf(rs.getObject("origin_x").toString());
				int originY = Integer.valueOf(rs.getObject("origin_y").toString());
				int destinationX = Integer.valueOf(rs.getObject("destination_x").toString());
				int destinationY = Integer.valueOf(rs.getObject("destination_y").toString());
				int result = Integer.valueOf(rs.getObject("result").toString());
				actions.add(new Action(originX, originY, destinationX, destinationY, result));
			}
			
			array = new Action[actions.size()];
			for (int i = 0; i < actions.size(); i++)
			{
				array[i] = (Action) actions.get(i);
			}
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		finally
		{
			return array;
		}
	}
	
	//Returns all plays in the database.
	//Currently exhibiting some strange behavior, use the hacked version instead, and take the Match's children as the array of plays
	public Play[] getAllPlays()
	{
		ArrayList<Action> actions = new ArrayList<Action>();
		ArrayList<Play> plays = new ArrayList<Play>();
		Play[] array = null;
		try
		{
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from play_info order by play_id, halfplay_id, action_id");
			int currentPlayID = -1;
			while (rs.next())
			{
				if (currentPlayID == Integer.valueOf(rs.getObject("play_id").toString()))
				{
					int actionID = Integer.valueOf(rs.getObject("action_id").toString());
					int originX = Integer.valueOf(rs.getObject("origin_x").toString());
					int originY = Integer.valueOf(rs.getObject("origin_y").toString());
					int destinationX = Integer.valueOf(rs.getObject("destination_x").toString());
					int destinationY = Integer.valueOf(rs.getObject("destination_y").toString());
					int result = Integer.valueOf(rs.getObject("action_result").toString());
					actions.add(new Action(actionID, originX, originY, destinationX, destinationY, result));
				}
				else
				{
					if (plays.size() > 0)
					{
						ArrayList<Action> children = new ArrayList<Action>();
						for (int i = 0; i < actions.size(); i++)
						{
							children.add(actions.get(i));
						}
						
						plays.get(plays.size() - 1).Children = children;
					}
					actions.clear();
					currentPlayID = Integer.valueOf(rs.getObject("play_id").toString());
					plays.add(new Play(currentPlayID));
					
				}
				
				if (plays.size() > 0)
				{
					ArrayList<Action> children = new ArrayList<Action>();
					for (int i = 0; i < actions.size(); i++)
					{
						children.add(actions.get(i));
					}
					
					plays.get(plays.size() - 1).Children = children;
				}

			}
			
			array = new Play[plays.size()];
			for (int i = 0; i < plays.size(); i++)
			{
				array[i] = plays.get(i);
			}

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		finally
		{
			return array;
		}
	}
	
	//Returns all plays in the database.
	//Currently exhibiting some strange behavior, use the hacked version instead, and take the Match's children as the array of plays
	public Play[] getPlays(int rotation)
	{
		ArrayList<Action> actions = new ArrayList<Action>();
		ArrayList<Play> plays = new ArrayList<Play>();
		Play[] array = null;
		try
		{
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from play_info where rotation = " + rotation + " order by play_id, halfplay_id, action_id");
			int currentPlayID = -1;
			while (rs.next())
			{
				if (currentPlayID == Integer.valueOf(rs.getObject("play_id").toString()))
				{
					int actionID = Integer.valueOf(rs.getObject("action_id").toString());
					int originX = Integer.valueOf(rs.getObject("origin_x").toString());
					int originY = Integer.valueOf(rs.getObject("origin_y").toString());
					int destinationX = Integer.valueOf(rs.getObject("destination_x").toString());
					int destinationY = Integer.valueOf(rs.getObject("destination_y").toString());
					int result = Integer.valueOf(rs.getObject("action_result").toString());
					actions.add(new Action(actionID, originX, originY, destinationX, destinationY, result));
				}
				else
				{
					if (plays.size() > 0)
					{
						ArrayList<Action> children = new ArrayList<Action>();
						for (int i = 0; i < actions.size(); i++)
						{
							children.add(actions.get(i));
						}
						
						plays.get(plays.size() - 1).Children = children;
					}
					actions.clear();
					currentPlayID = Integer.valueOf(rs.getObject("play_id").toString());
					plays.add(new Play(currentPlayID));
					
				}
				
				if (plays.size() > 0)
				{
					ArrayList<Action> children = new ArrayList<Action>();
					for (int i = 0; i < actions.size(); i++)
					{
						children.add(actions.get(i));
					}
					
					plays.get(plays.size() - 1).Children = children;
				}

			}
			
			array = new Play[plays.size()];
			for (int i = 0; i < plays.size(); i++)
			{
				array[i] = plays.get(i);
			}

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		finally
		{
			return array;
		}
	}
	
	//Look up a match by its id
	public Match lookUpMatch(int id)
	{
		Match match = new Match(id);
		match.children = new ArrayList<Play>();
		try
		{
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from match_info where match_id = " + id + " order by match_id, play_id, action_id");
			int playId = -1;
			while (rs.next())
			{
				if (((java.math.BigDecimal)rs.getObject("play_id")).intValue()  == playId)
				{
					//add a new action to the current play
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
				}
				else
				{
					//Add a new play to the match
					match.children.add(new Play(playId));
					match.children.get(match.children.size() - 1).Children = new ArrayList<Action>();
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
					
					playId = ((java.math.BigDecimal)rs.getObject("play_id")).intValue();
				}
				
			}

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		return match;
	}
	
	//A quick hack based off of lookupMatch to return all the plays in the database as the children of a match
	//Very reliable, but should be refactored to return Play[] instead of Match
	public Match getAllPlays_HACK()
	{
		Match match = new Match();
		match.children = new ArrayList<Play>();
		try
		{
			//Find all plays in every match, and add them to one big match
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from match_info order by match_id, play_id, action_id");
			int playId = -1;
			while (rs.next())
			{
				if (((java.math.BigDecimal)rs.getObject("play_id")).intValue()  == playId)
				{
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
				}
				else
				{
					
					match.children.add(new Play(playId));
					match.children.get(match.children.size() - 1).Children = new ArrayList<Action>();
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
					
					playId = ((java.math.BigDecimal)rs.getObject("play_id")).intValue();
				}
				
			}
			

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
		}
		return match;
	}
	
	//A quick hack based off of lookupMatch to return all the plays in a rotation in the database as the children of a match
	//Very reliable, but should be refactored to return Play[] instead of Match
	public Match getPlaysFromRotation_HACK(int rotation)
	{
		Match match = new Match();
		match.children = new ArrayList<Play>();
		try
		{
			Statement query = con.createStatement();
			ResultSet rs = query.executeQuery("select * from match_info where rotation = " + rotation + " order by match_id, play_id, action_id");
			int playId = -1;
			while (rs.next())
			{
				if (((java.math.BigDecimal)rs.getObject("play_id")).intValue()  == playId)
				{
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
				}
				else
				{
					//Play temp = new Play(playId);
					match.children.add(new Play(playId));
					//ArrayList<Action> tempActs = new ArrayList<Action>();
					//temp.Children = tempActs;
					match.children.get(match.children.size() - 1).Children = new ArrayList<Action>();
					match.children.get(match.children.size() - 1).Children.add(new Action(((java.math.BigDecimal)rs.getObject("action_id")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_x")).intValue(), ((java.math.BigDecimal)rs.getObject("origin_y")).intValue(), 
							((java.math.BigDecimal)rs.getObject("destination_x")).intValue(), ((java.math.BigDecimal)rs.getObject("destination_y")).intValue(), ((java.math.BigDecimal)rs.getObject("action_result")).intValue()));
					
					playId = ((java.math.BigDecimal)rs.getObject("play_id")).intValue();
				}
				
			}
			
			//match.children = children;

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
			try
            {
            	//if there is a problem, rollback the changes
                con.rollback();
            }
            catch (SQLException ex)
            {
                System.err.println(ex); //if the rollback fails, just quit
            }
			
		}
		return match;
	}
}
